package com.turing.post.account.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.account.util.AccountSystemAddTradDetail;
import com.turing.post.account.util.AccountSystemAddTradDetailDao;
import com.turing.post.cardmanagement.util.ActivateCard;
import com.turing.post.cardmanagement.util.ActivateCardDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

@Stateless(mappedName = "account.impl.AccountSystemAddTradDetailDaoImpl")
@Remote
@Local
public class AccountSystemAddTradDetailDaoImpl implements
		AccountSystemAddTradDetailDao {
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;
	@EJB(mappedName = "cardmanagement.impl.ActivateCardDaoImpl")
	private ActivateCardDao acDao;

	private static final String grpSumSql = ""
	// 销售
			+ "sum(case when t.tranType = '00' or t.tranType = '10' then t.creditAmount - t.debitAmount else 0 end), "
			// 充值
			+ "sum(case when t.tranType = '02' or t.tranType = '12' then t.creditAmount - t.debitAmount else 0 end), "
			// 消费
			+ "sum(case when t.tranType = '01' or t.tranType = '11' then t.debitAmount - t.creditAmount else 0 end), "
			// 赎回
			+ "sum(case when t.tranType = '03' or t.tranType = '13' then t.debitAmount - t.creditAmount else 0 end), "
			// 手续费
			+ "sum(case when t.tranType = '04' or t.tranType = '14' then t.debitAmount - t.creditAmount else 0 end), "
			// 调整
			+ "sum(case when t.tranType = '99' then t.creditAmount - t.debitAmount else 0 end) ";

	/**
	 * 插入帐户子系统交易流水记录
	 */
	public void insert(AccountSystemAddTradDetail asatd)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, asatd);
	}

	/**
	 * 检索所有的附加子帐户记帐信息
	 * 
	 * @param accSeqNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<AccountSystemAddTradDetail> getAllAccountSystemAddTradDetailByAccSeqNo(
			String accSeqNo) {
		Query query = _em
				.createNativeQuery(
						"select t.* from AccountSystemAddTradDetail t where t.accSeqNo = ?1 order by AccSubSeqNo",
						AccountSystemAddTradDetail.class);
		query.setParameter(1, accSeqNo);
		return query.getResultList();
	}

	/**
	 * 按销售机构号、卡种类分类汇总某发卡商某日某销售商下属销售机构记帐流水
	 * 
	 * @param issueCompanyNo
	 *            发卡商NO
	 * @param tranDate
	 *            帐务日期
	 * @param issueBranchNo
	 *            发卡商分支机构
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<AccountSystemAddTradDetail> sumAllAccountSystemAddTradDetailByBranchAndCardClassic(
			String issueCompanyNo, Date tranDate, String issueBranchNo) {
		Query query = _em
				.createNativeQuery("select t.openBranchNo, t.cardClassicId, "
						+ grpSumSql
						+ "from AccountSystemAddTradDetail t, SysBranchSubord s "
						+ "where t.issueCompanyNo = ?1 and t.tranDate = ?2 and "
						+ "t.openBranchNo = s.SubBranchNo and "
						+ "s.BranchNo = ?3 and s.SubBranchType = '12' "
						+ "group by t.openBranchNo, t.cardClassicId "
						+ "order by t.openBranchNo, t.cardClassicId ");
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, tranDate);
		query.setParameter(3, issueBranchNo);
		List<Object[]> obj_list = query.getResultList();
		if (obj_list == null)
			return null;

		// 类型转换
		List<AccountSystemAddTradDetail> asatd_list = new ArrayList<AccountSystemAddTradDetail>();
		for (Object[] objarr : obj_list) {
			AccountSystemAddTradDetail asatd = new AccountSystemAddTradDetail();
			asatd.setOpenBranchNo(objarr[0].toString());
			asatd.setCardClassicId(objarr[1] == null ? null : new BigInteger(
					objarr[1].toString()));
			asatd.setSaleAmount(new BigInteger(objarr[2].toString()));
			asatd.setDepositAmount(new BigInteger(objarr[3].toString()));
			asatd.setConsumeAmount(new BigInteger(objarr[4].toString()));
			asatd.setRedemingAmount(new BigInteger(objarr[5].toString()));
			asatd.setFeeAmount(new BigInteger(objarr[6].toString()));
			asatd.setAdjustAmount(new BigInteger(objarr[7].toString()));
			asatd_list.add(asatd);
		}

		return asatd_list;
	}

	/**
	 * 按销售机构号分类汇总某发卡商某日某分支机构下属销售机构记帐流水
	 * 
	 * @param issueCompanyNo
	 *            发卡商NO
	 * @param tranDate
	 *            帐务日期
	 * @param issueBranchNo
	 *            机构号
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<AccountSystemAddTradDetail> sumAllAccountSystemAddTradDetailByBranch(
			String issueCompanyNo, Date tranDate, String issueBranchNo) {
		Query query = _em.createNativeQuery("select t.openBranchNo, "
				+ grpSumSql
				+ "from AccountSystemAddTradDetail t, SysBranchSubord s "
				+ "where t.issueCompanyNo = ?1 and t.tranDate = ?2 and "
				+ "t.openBranchNo = s.SubBranchNo and "
				+ "s.BranchNo = ?3 and s.SubBranchType = '12' "
				+ "group by t.openBranchNo " + "order by t.openBranchNo ");
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, tranDate);
		query.setParameter(3, issueBranchNo);
		List<Object[]> obj_list = query.getResultList();
		if (obj_list == null)
			return null;

		// 类型转换
		List<AccountSystemAddTradDetail> asatd_list = new ArrayList<AccountSystemAddTradDetail>();
		for (Object[] objarr : obj_list) {
			AccountSystemAddTradDetail asatd = new AccountSystemAddTradDetail();
			asatd.setOpenBranchNo(objarr[0].toString());
			asatd.setSaleAmount(new BigInteger(objarr[1].toString()));
			asatd.setDepositAmount(new BigInteger(objarr[2].toString()));
			asatd.setConsumeAmount(new BigInteger(objarr[3].toString()));
			asatd.setRedemingAmount(new BigInteger(objarr[4].toString()));
			asatd.setFeeAmount(new BigInteger(objarr[5].toString()));
			asatd.setAdjustAmount(new BigInteger(objarr[6].toString()));
			asatd_list.add(asatd);
		}

		return asatd_list;
	}

	/**
	 * 根据输入条件,查询账户交易附加流水(pcps和自助网站:卡交易明细查询)
	 * 
	 * @param webInput
	 * @param cardNo
	 * @param startTime
	 * @param endTime
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<AccountSystemAddTradDetail> getAccountSystemAddTradDetailByCondition(
			PCPSWebInput webInput, String cardNo, String startTime,
			String endTime, Integer page) {
		if (webInput == null)
			return null;
		// 判断登录用户输入卡号(序号)是否属于该发卡商
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		ActivateCard ac = acDao.getActivateCardByCardNoOrSeqNo(cardNo);
		if (ac == null) {
			return null;
		}
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		Query query = _em
				.createNativeQuery(
						"select t.* "
								+ "from accountsystemaddtraddetail t "
								+ "where t.cardno=?1 "
								+ "and t.issuecompanyno=?4"
								+ "and t.trantime>=to_date(?2,'yyyy-mm-dd hh24:mi:ss') "
								+ "and t.trantime<=to_date(?3,'yyyy-mm-dd hh24:mi:ss') "
								+ "order by t.trantime desc,t.accseqno desc,t.AccSubSeqNo desc",
						AccountSystemAddTradDetail.class).setFirstResult(
						startPosition).setMaxResults(maxResult);
		query.setParameter(1, ac.getCardNo());
		query.setParameter(4, issuecompanyno);
		if (PCPSUtil.isEmpty(startTime)) {
			query.setParameter(2, "");
		} else {
			query.setParameter(2, startTime + " 00:00:00");
		}
		if (PCPSUtil.isEmpty(endTime)) {
			query.setParameter(3, "");
		} else {
			query.setParameter(3, endTime + " 23:59:59");
		}
		return query.getResultList();
	}

	/**
	 * 根据输入条件,查询账户交易附加流水(pcps和自助网站:卡交易明细查询) 总数
	 * 
	 * @param webInput
	 * @param cardNo
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	@Override
	public BigInteger getAccountSystemAddTradDetailTotalByCondition(
			PCPSWebInput webInput, String cardNo, String startTime,
			String endTime) {
		if (webInput == null)
			return null;
		// 判断登录用户输入卡号(序号)是否属于该发卡商
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		ActivateCard ac = acDao.getActivateCardByCardNoOrSeqNo(cardNo);
		if (ac == null) {
			return null;
		}
		Query query = _em.createNativeQuery("select nvl(count(*),0) "
				+ "from accountsystemaddtraddetail t where t.cardno=?1 "
				+ "and t.trantime>=to_date(?2,'yyyy-mm-dd hh24:mi:ss') "
				+ "and t.trantime<=to_date(?3,'yyyy-mm-dd hh24:mi:ss') ");
		query.setParameter(1, ac.getCardNo());
		query.setParameter(4, issuecompanyno);
		if (PCPSUtil.isEmpty(startTime)) {
			query.setParameter(2, "");
		} else {
			query.setParameter(2, startTime + " 00:00:00");
		}
		if (PCPSUtil.isEmpty(endTime)) {
			query.setParameter(3, "");
		} else {
			query.setParameter(3, endTime + " 23:59:59");
		}
		return new BigInteger(query.getSingleResult().toString());
	}
}